import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random
import os
import matplotlib.pyplot as plt
import seaborn as sns

# 1. 模拟数据生成函数
def generate_insurance_data():
    # 生成日期范围：2024年全年数据
    dates = pd.date_range(start='2024-01-01', end='2024-12-31')
    regions = ['华东', '华北', '华南', '华中', '西部']
    policy_types = ['车险', '财产险', '责任险', '意外险', '健康险']
    channels = ['直销', '代理', '经纪', '线上', '银保']
    companies = ['公司A', '公司B', '公司C', '公司D', '公司E']

    # 营销员数据
    agents = pd.DataFrame({
        'agent_id': [f'AG{str(i).zfill(5)}' for i in range(1, 101)],
        'join_date': [random.choice(dates) for _ in range(100)],
        'region': [random.choice(regions) for _ in range(100)],
        'status': ['active'] * 100
    })

    # 保单数据
    policies = pd.DataFrame({
        'policy_id': [f'PL{str(i).zfill(8)}' for i in range(1, 1001)],
        'issue_date': [random.choice(dates) for _ in range(1000)],
        'policy_type': [random.choice(policy_types) for _ in range(1000)],
        'premium': [round(random.uniform(1000, 50000), 2) for _ in range(1000)],
        'insured_amount': [round(random.uniform(5000, 1000000), 2) for _ in range(1000)],
        'channel': [random.choice(channels) for _ in range(1000)],
        'region': [random.choice(regions) for _ in range(1000)],
        'is_new': [random.choice([True, False]) for _ in range(1000)],
        'agent_id': [random.choice(agents['agent_id']) for _ in range(1000)]
    })

    # 再保险数据
    reinsurances = pd.DataFrame({
        'policy_id': random.choices(policies['policy_id'], k=300),
        'ceded_premium': [round(random.uniform(100, 10000), 2) for _ in range(300)],
        'company': [random.choice(companies) for _ in range(300)],
        'is_affiliated': [random.choice([True, False]) for _ in range(300)],
        'is_domestic': [random.choice([True, False]) for _ in range(300)],
        'treaty_type': [random.choice(['临分', '合同']) for _ in range(300)],
        'proportion_type': [random.choice(['比例', '非比例']) for _ in range(300)]
    })

    return agents, policies, reinsurances


# 2. 指标计算函数
def calculate_kpis(agents, policies, reinsurances, report_date):
    # 转换为日期格式
    if isinstance(report_date, str):
        report_date = datetime.strptime(report_date, '%Y-%m-%d')

    # 时间范围计算
    seven_days_ago = report_date - timedelta(days=7)
    month_start = report_date.replace(day=1)
    year_start = report_date.replace(month=1, day=1)

    # 筛选时间范围内的数据
    policies_7d = policies[pd.to_datetime(policies['issue_date']).between(seven_days_ago, report_date)]
    policies_1m = policies[pd.to_datetime(policies['issue_date']).between(month_start, report_date)]
    policies_1y = policies[pd.to_datetime(policies['issue_date']).between(year_start, report_date)]

    # 营销员脱落率计算
    def agent_attrition(period_df, period_name):
        # 期初营销员数量
        start_date = period_df['issue_date'].min()
        active_agents_start = agents[pd.to_datetime(agents['join_date']) <= start_date]

        # 新增营销员数量
        new_agents = agents[pd.to_datetime(agents['join_date']).between(start_date, report_date)]

        # 离职营销员数量
        # 在模拟数据中随机设置20%的营销员离职
        leave_agents = agents.sample(frac=0.2)

        attrition_rate = len(leave_agents) / (len(active_agents_start) + (len(new_agents) - len(leave_agents))) * 100
        return pd.DataFrame({
            '指标': ['营销员脱落率'],
            '值': [round(attrition_rate, 2)],
            '周期': [period_name],
            '单位': ['%']
        })

    # 新单保额与新单保费比
    def new_premium_ratio(period_df, period_name):
        new_policies = period_df[period_df['is_new']]
        if len(new_policies) > 0:
            ratio = new_policies['insured_amount'].sum() / new_policies['premium'].sum()
        else:
            ratio = 0
        return pd.DataFrame({
            '指标': ['新单保额与新单保费比'],
            '值': [round(ratio, 2)],
            '周期': [period_name],
            '单位': ['比值']
        })

    # 分险种保费占比
    def policy_type_share(period_df, period_name):
        total_premium = period_df['premium'].sum()
        if total_premium > 0:
            result = period_df.groupby('policy_type')['premium'].sum() / total_premium * 100
            return pd.DataFrame({
                '指标': ['分险种保费占比 - ' + idx for idx in result.index],
                '值': result.round(2).values,
                '周期': [period_name] * len(result),
                '单位': ['%'] * len(result)
            })
        return pd.DataFrame()

    # 所有指标计算
    def calculate_all_metrics():
        metrics = []
        for period_df, period_name in [(policies_7d, '7日'), (policies_1m, '月度'), (policies_1y, '年度')]:
            metrics.append(agent_attrition(period_df, period_name))
            metrics.append(new_premium_ratio(period_df, period_name))
            metrics.append(policy_type_share(period_df, period_name))
        return pd.concat(metrics, ignore_index=True)

    return calculate_all_metrics()


# 3. 图表生成函数
def generate_and_save_charts(kpi_results, report_date, policies):
    # 创建主输出目录
    main_dir = f"财产保险业务看板_{report_date}"
    os.makedirs(main_dir, exist_ok=True)

    # 设置图表样式
    sns.set_theme(style="whitegrid")
    plt.rcParams['font.sans-serif'] = ['SimHei']  # 解决中文显示问题
    plt.rcParams['axes.unicode_minus'] = False  # 解决负号显示问题

    # 1. 营销员脱落率图表
    attrition_data = kpi_results[kpi_results['指标'] == '营销员脱落率']
    if not attrition_data.empty:
        dir_path = os.path.join(main_dir, "营销员脱落率")
        os.makedirs(dir_path, exist_ok=True)

        # 柱状图
        plt.figure(figsize=(10, 6))
        sns.barplot(x='周期', y='值', data=attrition_data, palette="Blues_d")
        plt.title('不同周期营销员脱落率对比')
        plt.ylabel('脱落率 (%)')
        plt.savefig(os.path.join(dir_path, '营销员脱落率对比.png'))
        plt.close()

    # 2. 新单保额与新单保费比图表
    premium_ratio_data = kpi_results[kpi_results['指标'] == '新单保额与新单保费比']
    if not premium_ratio_data.empty:
        dir_path = os.path.join(main_dir, "新单保额与新单保费比")
        os.makedirs(dir_path, exist_ok=True)

        # 折线图
        plt.figure(figsize=(10, 6))
        sns.lineplot(x='周期', y='值', data=premium_ratio_data, marker='o', linewidth=2.5)
        plt.title('不同周期保额与保费比变化趋势')
        plt.ylabel('保额/保费比')
        plt.grid(True, linestyle='--', alpha=0.7)
        plt.savefig(os.path.join(dir_path, '保额保费比趋势.png'))
        plt.close()

    # 3. 分险种保费占比图表
    policy_share_data = kpi_results[kpi_results['指标'].str.contains('分险种保费占比')]
    if not policy_share_data.empty:
        dir_path = os.path.join(main_dir, "分险种保费占比")
        os.makedirs(dir_path, exist_ok=True)

        # 按周期分组绘制饼图
        for period in policy_share_data['周期'].unique():
            period_data = policy_share_data[policy_share_data['周期'] == period]

            # 提取险种名称
            policy_types = period_data['指标'].str.replace('分险种保费占比 - ', '')
            values = period_data['值']

            # 饼图
            plt.figure(figsize=(10, 8))
            plt.pie(values, labels=policy_types, autopct='%1.1f%%', startangle=90,
                    colors=sns.color_palette('Set3'), wedgeprops={'edgecolor': 'w', 'linewidth': 1})
            plt.title(f'{period}分险种保费占比')
            plt.axis('equal')
            plt.savefig(os.path.join(dir_path, f'{period}险种占比.png'))
            plt.close()

        # 所有周期的堆叠柱状图
        pivot_data = policy_share_data.copy()
        pivot_data['险种'] = pivot_data['指标'].str.replace('分险种保费占比 - ', '')
        pivot_data = pivot_data.pivot_table(index='周期', columns='险种', values='值', aggfunc='sum')

        plt.figure(figsize=(12, 8))
        pivot_data.plot(kind='bar', stacked=True, colormap='viridis', edgecolor='black')
        plt.title('不同周期险种保费占比对比')
        plt.ylabel('占比 (%)')
        plt.xlabel('周期')
        plt.xticks(rotation=0)
        plt.legend(title='险种', bbox_to_anchor=(1.05, 1), loc='upper left')
        plt.tight_layout()
        plt.savefig(os.path.join(dir_path, '险种占比对比.png'))
        plt.close()

    # 4. 保费分布图表（使用原始数据）
    dir_path = os.path.join(main_dir, "保费分布")
    os.makedirs(dir_path, exist_ok=True)

    # 保费分布直方图
    plt.figure(figsize=(10, 6))
    sns.histplot(policies['premium'], bins=30, kde=True, color='skyblue')
    plt.title('保费金额分布')
    plt.xlabel('保费金额')
    plt.ylabel('保单数量')
    plt.savefig(os.path.join(dir_path, '保费分布直方图.png'))
    plt.close()

    # 不同渠道保费对比
    plt.figure(figsize=(12, 7))
    sns.boxplot(x='channel', y='premium', data=policies, palette='pastel')
    plt.title('不同销售渠道保费金额对比')
    plt.xlabel('销售渠道')
    plt.ylabel('保费金额')
    plt.xticks(rotation=45)  # 旋转45度防止重叠
    plt.tight_layout()  # 自动调整布局
    plt.savefig(os.path.join(dir_path, '渠道保费对比.png'))
    plt.close()

    print(f"图表已保存到目录: {main_dir}")

# 3. 主程序
def main():
    # 设置报告日期
    report_date = '2024-12-31'

    # 生成模拟数据
    print("正在生成模拟数据...")
    agents, policies, reinsurances = generate_insurance_data()

    # 计算业务指标
    print("正在计算业务指标...")
    kpi_results = calculate_kpis(agents, policies, reinsurances, report_date)

    # 导出指标到Excel
    output_file = f"财产保险业务看板_{report_date}/业务指标_{report_date}.xlsx"
    os.makedirs(os.path.dirname(output_file), exist_ok=True)
    kpi_results.to_excel(output_file, index=False)
    print(f"指标数据已导出至: {output_file}")

    # 生成并保存图表
    print("正在生成图表...")
    generate_and_save_charts(kpi_results, report_date, policies)

    # 显示部分结果
    print("\n指标概览:")
    print(kpi_results.head(10))


if __name__ == "__main__":
    main()